#include "sqlhelper.h"

SQLHelper::SQLHelper(QObject *parent) : QObject(parent) {
    // 构造函数中的初始化代码
    qDebug() << "hello SQLite~~" ;
}

SQLHelper::~SQLHelper() {
    // 析构函数中的清理代码
    qDebug() << "BYe SQLite~~" ;
}

QSqlDatabase SQLHelper::createConnection(const QString &dbName) {
    bool flag = false;
    // 初始化数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbName);
    flag = true;
    return db;
}

bool SQLHelper::openConnection(QSqlDatabase db) {
    qDebug() << "DB Connected~~~" ;
    return db.open();
}

void SQLHelper::closeConnection(QSqlDatabase db) {
    if (db.isOpen()) {
        db.close();
        qDebug() << "DB Closed~~~" ;
    }
}

int SQLHelper::executeQuery(QSqlDatabase db, const QString &query) {
    if (!openConnection(db)) {
        // 打开数据库连接失败
        qDebug() << "Failed to open database connection.";
        return -1;
    }
    int result = -1;
    // 假设db是一个已经设置好的QSqlDatabase实例
    QSqlQuery sql(db);
    // 准备查询特定日期的记录总数
    sql.prepare(query);
    // 执行查询
    if (!sql.exec()) {
        qDebug() << "Query failed:" << sql.lastError();
    } else {
        // 转到查询结果的第一行
        sql.next();
        // 读取记录总数
        result = sql.value(0).toInt();
        // 输出结果
        qDebug() << "Total records for date" << query << ":" << result;
    }
    closeConnection(db);
    return result;
}

bool SQLHelper::executeInsert(QSqlDatabase db, const QString &query, const QStringList &values) {
    if (!openConnection(db)) {
        // 打开数据库连接失败
        qDebug() << "Failed to open database connection.";
        return false;
    }
    QSqlQuery sql;
    sql.prepare(query);
    for (int i = 0; i < values.size(); ++i) {
        sql.bindValue(i, values.at(i));
    }
    if (!sql.exec()) {
        // 插入执行失败
        qDebug() << "Insert execution failed:" << sql.lastError().text();
        closeConnection(db);
        return false;
    }
    closeConnection(db);
    return true;
}


int SQLHelper::queryEventCountByDate(QSqlDatabase db, const QString &date) {
    QString query = QString("SELECT COUNT(*) FROM tb_event WHERE Date = '%1'").arg(date);
    qDebug() << query;
    return executeQuery(db, query);
}

bool SQLHelper::insertEvent(QSqlDatabase db, const QString &eventId, const QString &date, const QString &event, int status) {
    QString query = "INSERT INTO tb_event (EventID, Date, Event, Status) VALUES (?, ?, ?, ?)";
    return executeInsert(db, query,
                         QStringList() << eventId << date << event << QString::number(status));

//    QString query = "INSERT INTO tb_event (id, event, date, status) VALUES (?, ?, ?, ?)";
//    QSqlQuery queryResult;
//    queryResult.prepare(query);
//    queryResult.bindValue(0, eventId);
//    queryResult.bindValue(1, event);
//    queryResult.bindValue(2, date); // QDate::currentDate().toString("yyyy-MM-dd")
//    queryResult.bindValue(3, status);
//    return queryResult.exec();
}

QList<EventInfo> SQLHelper::queryEventsByDate(QSqlDatabase db, const QString &date) {
    QList<EventInfo> events;
    if (!openConnection(db)) {
        // 打开数据库连接失败
        qDebug() << "Failed to open database connection.";
        return events;
    }

    QString query = QString("SELECT EventID, Event, Status FROM tb_event WHERE Date = '%1'").arg(date);

    QSqlQuery sql(db);
    if (sql.exec(query)) {
        while (sql.next()) {
            EventInfo info;
            info.eventID = sql.value(0).toString();
            info.event = sql.value(1).toString();
            info.status = sql.value(2).toInt();
            events.append(info); // 这里尝试添加 EventInfo 类型的元素
        }
    } else {
        qDebug() << "Query failed:" << sql.lastError();
    }
    closeConnection(db);
    return events;
}

bool SQLHelper::updateStatusById(QSqlDatabase db, const QString& eventId, int newStatus) {
    QString query = "UPDATE tb_event SET Status = :newStatus WHERE EventID = :eventId";
    // 打开数据库连接
    if (!openConnection(db)) {
        qDebug() << "Failed to open database connection.";
        return false;
    }
    // 创建查询对象
    QSqlQuery sql;
    if (!sql.prepare(query)) {
        qDebug() << "Failed to prepare query:" << sql.lastError();
        closeConnection(db);
        return false;
    }
    // 绑定新状态到命名参数 :newStatus
    sql.bindValue(":newStatus", newStatus);
    // 绑定事件ID到命名参数 :eventId
    sql.bindValue(":eventId", eventId);
    // 执行更新操作
    if (!sql.exec()) {
        qDebug() << "Update execution failed:" << sql.lastError().text();
        closeConnection(db);
        return false;
    }
    // 关闭数据库连接
    closeConnection(db);
    // 返回更新操作是否成功
    return true;
}
